package com.xxd.spring.boot.demo.hexinglei.controller;

import com.xxd.spring.boot.demo.hexinglei.Result;
import com.xxd.spring.boot.demo.hexinglei.entity.StudentEntity;
import com.xxd.spring.boot.demo.hexinglei.mapper.StudentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


import java.util.ArrayList;
import java.util.List;
import java.sql.*;

@RestController
@RequestMapping("/student")
public class StudentController {
    //查信息
    @Autowired
    private StudentMapper studentMapper;

    @RequestMapping(value="/liststudent")
    public Result listStudent(@RequestBody(required = false) StudentEntity studentEntity) throws SQLException  {
        boolean flag = true;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Result result = new Result().success();
        String num = studentEntity.getNum();
        String name = studentEntity.getName();
        String sex = studentEntity.getSex();
        String addrress = studentEntity.getAddress();

        List<StudentEntity> sD = new ArrayList<>();
        StudentEntity rD = new StudentEntity();
        try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                String user = "root";
                String pwd = "w580580";
                String url = "jdbc:mysql://localhost:3306/stumanager?serverTimezone=UTC";
                con = DriverManager.getConnection(url, user, pwd);
                String sql = "select * from student_system where num=? or stu_name=? or sex=? or address=? ";
                ps = con.prepareStatement(sql);
                ps.setObject(1, num);
                ps.setObject(2, name);
                ps.setObject(3, sex);
                ps.setObject(4, addrress);
                rs = ps.executeQuery();
                while(rs.next()) {
                    rD.setNum(rs.getString("num"));
                    rD.setName(rs.getString("stu_name"));
                    rD.setSex(rs.getString("sex"));
                    rD.setAddress(rs.getString("address"));
                    sD.add(rD);
                    flag = true;
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (con != null) con.close();
                if (ps != null) ps.close();
                if (rs != null) rs.close();
            }
        if(!flag){
            result.setMessage("查询学生不存在！");
        }else{
            result.setData(sD);
        }
        return result;
    }

    //添加信息
    @RequestMapping(value="/addstudent")
    public Result addStudent(@RequestBody StudentEntity studentEntity) throws SQLException{
        boolean flag = false;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Result result = new Result().success();
        String num = studentEntity.getNum();
        String name = studentEntity.getName();
        String sex = studentEntity.getSex();
        String address = studentEntity.getAddress();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String user = "root";
            String pwd = "w580580";
            String url = "jdbc:mysql://localhost:3306/stumanager?serverTimezone=UTC";
            con = DriverManager.getConnection(url, user, pwd);
            String sql1 = "select * from student_system where num=?";
            String sql2 = "insert into student_system (num,stu_name,sex,address) values (?,?,?,?) ";
            ps = con.prepareStatement(sql1);
            ps.setObject(1, num);
            rs = ps.executeQuery();
            if(!rs.next()){
                rs.close();
                ps.close();
                ps = con.prepareStatement(sql2);
                ps.setObject(1, num);
                ps.setObject(2, name);
                ps.setObject(3, sex);
                ps.setObject(4, address);
                ps.execute();
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) con.close();
            if (ps != null) ps.close();
            if (rs != null) rs.close();
        }
        if(!flag){
            result.setMessage("学号重复！");
        }else{
            result.setMessage("添加成功！");
        }
        return result;
    }

    //删除信息
    @RequestMapping(value="/delstudent/{num}")
    public Result delStudent(@PathVariable("num") String num) throws SQLException  {
        boolean flag = false;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Result result = new Result().success();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String user = "root";
            String pwd = "w580580";
            String url = "jdbc:mysql://localhost:3306/stumanager?serverTimezone=UTC";
            con = DriverManager.getConnection(url, user, pwd);
            String sql = "select * from student_system where num="+num;
            String sql2 ="delete from student_system where num="+num;
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                rs.close();
                ps.close();
                ps = con.prepareStatement(sql2);
                ps.execute();
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) con.close();
            if (ps != null) ps.close();
            if (rs != null) rs.close();
        }
        if(!flag){
            result.setMessage("学号不存在！");
        }else{
            result.setMessage("删除成功！");
        }
        return result;
    }

    //修改
    @RequestMapping(value="/updatestudent")
    public Result delStudent(@RequestBody StudentEntity studentEntity) throws SQLException  {
        boolean flag = false;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Result result = new Result().success();
        String num = studentEntity.getNum();
        String name = studentEntity.getName();
        String sex = studentEntity.getSex();
        String addrress = studentEntity.getAddress();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String user = "root";
            String pwd = "w580580";
            String url = "jdbc:mysql://localhost:3306/stumanager?serverTimezone=UTC";
            con = DriverManager.getConnection(url, user, pwd);
            String sql = "select * from student_system where num=?";
            String sql2 = "update student_system set stu_name=?,sex=?,address=?";
            ps = con.prepareStatement(sql);
            ps.setObject(1, num);
            rs = ps.executeQuery();
            if(true){
                ps.close();
                ps = con.prepareStatement(sql2);
                ps.setObject(1, name);
                ps.setObject(2, sex);
                ps.setObject(3, addrress);
                ps.execute();
                flag = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (con != null) con.close();
            if (ps != null) ps.close();
            if (rs != null) rs.close();
        }
        if(!flag){
            result.setMessage("学号不存在！");
        }else{
            result.setMessage("修改成功！");
        }
        return result;
    }
}
